/*stuinfo.c*/
#include "mysql.h"
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

struct student // ��Ӧ�ڱ���������
{
  int id;           // ѧ��
  char name[20];    // ����
  char sex[6];      // �Ա�
  char mailbox[40]; // �����ַ
  char studep[100]; // ����Ժϵ
};

/*��ʼ�����ݿ�*/
MYSQL *initial(MYSQL *con)
{

  if (mysql_library_init(0, NULL, NULL))
  {
    fprintf(stderr, "Could not initialize MySQL library.\n");
    return;
  }
  con = mysql_init(NULL);
  return con;
}
/*�������ݿ�*/
MYSQL *myconnect(MYSQL *con, char *username, char *passwd, char *hostip, char *db)
{

  if (mysql_real_connect(con, "localhost", "root", "root", db, 0, NULL, 0) == NULL)
  {
    printf("connect failed\n");
  }
  return con;
}

void createDB(MYSQL *con, char *cmd)
{

  if (mysql_query(con, cmd))
  {
    printf("createDB failed");
  }
  else
    printf("Create Database successful\n");
}
void createTable(MYSQL *con, char *db, char *cmd)
{

  if (mysql_select_db(con, db))
  {
    printf("select DB failed\n");
  }
  if (mysql_query(con, cmd))
  {
    printf("createTAble failed\n");
  }
  else
    printf("Create table successful\n");
}
void displayTable(MYSQL *con, char *query)
{
  MYSQL_RES *mysql_res;
  MYSQL_ROW mysql_row;
  int i, j;
  int num_row, num_col;
  if ((mysql_real_query(con, query, (unsigned int)strlen(query))) != 0)
  {
    printf("Query failed\n");
  }
  // �����ѯ���
  if ((mysql_res = mysql_store_result(con)) == NULL)
  {
    printf("Store result failed\n");
  }
  // ��ȡ��ѯ���������������
  num_row = mysql_num_rows(mysql_res);
  num_col = mysql_num_fields(mysql_res);
  printf("row number = %1u, col number = %1u\n", num_row, num_col);
  printf("************************************************\n");
  // ������ʾ���
  for (i = 0; i < num_row; i++)
  {
    // �ӱ���Ĳ�ѯ����л�ȡ��һ������
    if ((mysql_row = mysql_fetch_row(mysql_res)) == NULL)
    {
      break;
    }
    // ��ȡ�г��ȵ�����
    mysql_fetch_lengths(mysql_res);
    for (j = 0; j < num_col; j++)
    {
      // printf("Column[%u] length=%1u\n", j, lengths[j]);
      //  ��ӡһ���и��е�ֵ
      printf("%s\t", mysql_row[j] ? mysql_row[j] : "NULL");
    }
    printf("\n");
  }
  printf("************************************************\n");
  // �ͷű���Ĳ�ѯ���
  mysql_free_result(mysql_res);
}
void insertData(MYSQL *con)
{
  struct student student1;
  char query[1024];
  printf("please input student id : \n");
  scanf("%d", &student1.id);
  getchar();
  printf("please input student name:\n ");
  scanf("%s", student1.name);
  getchar();
  printf("please input student sex(male/female):\n ");
  scanf("%s", student1.sex);
  getchar();
  printf("please input student phone: \n");
  scanf("%s", student1.mailbox);
  getchar();
  printf("please input student department: \n");
  scanf("%s", student1.studep);
  getchar();
  sprintf(query, "insert into stuinfo(id, stuname, gender,mailbox,studep) values(%d, '%s', '%s', '%s', '%s')", student1.id, student1.name, student1.sex, student1.mailbox, student1.studep);
  if ((mysql_real_query(con, query, (unsigned int)strlen(query))) != 0)
  {
    mysql_rollback(con);
    printf("insert failed\n");
  }
  mysql_commit(con);
  displayTable(con, "select * from stuinfo");
}
void deleteData(MYSQL *con)
{
  int id;
  char query[1024];
  printf("please input student id to be deleted\n");
  scanf("%d", &id);
  sprintf(query, "delete from stuinfo where id=%d", id);
  if ((mysql_real_query(con, query, (unsigned int)strlen(query))) != 0)
  {
    mysql_rollback(con);
    printf("delete failed\n");
  }
  mysql_commit(con);
  if (mysql_affected_rows(con) > 0)
    printf("delete successful\n");
  displayTable(con, "select * from stuinfo");
}
int menu()
{
  int choose;
  {
    printf("*********************************\n");
    printf("******student information system****\n");
    printf("*********************************\n");
    printf("1.  create database\n");
    printf("2.  create table\n");
    printf("3.  insert data\n");
    printf("4.  delete data\n");
    printf("5.  update data\n");
    printf("please input choose\n");
    scanf("%d", &choose);
    getchar();
    return choose;
  }
}
void handleMenu(MYSQL *mysql)
{
  int choose;
  while (1)
  {
    choose = menu();
    switch (choose)
    {
    case 1:
      createDB(mysql, "create database student");
      break;
    case 2:
      createTable(mysql, "student", "create table stuinfo(id INT, stuname TEXT, gender TEXT,mailbox TEXT,studep TEXT)");
      break;
    case 3:
      insertData(mysql);
      break;
    case 4:
      deleteData(mysql);
      break;
    case 5: // updateData(mysql); break;
    case 0:
      printf("exit\n");
      return;
      break;
    }
  }
}

int main(void)
{
  MYSQL *mysql_handle = NULL;
  mysql_handle = initial(mysql_handle);
  if (NULL == mysql_handle)
  {
    fprintf(stderr, "%s\n", "initialize failed");
    exit(1);
  }
  mysql_handle = myconnect(mysql_handle, "root", "root", "localhost", "");
  handleMenu(mysql_handle);
  mysql_close(mysql_handle);
  mysql_library_end();
  return (0);
}
